package com.mmd.sharding.persistence.repository;

import com.mmd.sharding.persistence.domain.TbOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author xch
 * 2022/7/20 21:17
 */
public interface TbOrderRepository extends JpaRepository<TbOrder, Long>, JpaSpecificationExecutor<TbOrder> {

    /**
     * 当分片键处于运算表达式中时，无法通过 SQL 字面 提取用于分片的值，将导致全路由。 例如，假设 create_time 为分片键：
     */
    @Query("select new TbOrder(s.orderId, s.userId) from TbOrder s where convert(s.userId, CHAR) = ?1")
    List<TbOrder> findAllByDemo(String id);



    /**
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_0 s where s.user_id in (?)) d  ::: [1]
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_1 s where s.user_id in (?)) d  ::: [1]
     */
    @Query(value = "select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order s where s.user_id in (?1)) d ", nativeQuery = true)
    List<TbOrder> findAllByDemo2(List<Long> userIds);


    /**
     * Actual SQL: ds_0 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_0 s where s.order_id in (?, ?, ?)) d where d.order_id =2 ::: [3, 10, 17]
     * Actual SQL: ds_0 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_1 s where s.order_id in (?, ?, ?)) d where d.order_id =2 ::: [3, 10, 17]
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_0 s where s.order_id in (?, ?, ?)) d where d.order_id =2 ::: [3, 10, 17]
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_1 s where s.order_id in (?, ?, ?)) d where d.order_id =2 ::: [3, 10, 17]
     */
    @Query(value = "select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order s where s.order_id in (?1)) d where d.order_id =2", nativeQuery = true)
    List<TbOrder> findAllByDemo3(List<Long> orderIds);

    /**
     * Actual SQL: ds_0 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_0 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_1 ::: select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     */
    @Query(value = "select d.order_id, d.user_id from ( select s.order_id, s.user_id from tb_order s ) d ", nativeQuery = true)
    List<TbOrder> findAllByDemo4(List<Long> orderIds);

    /**
     * 不支持的 SQL：
     * CASE WHEN 中包含子查询 验证
     * Actual SQL: ds_0 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_0 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     * Actual SQL: ds_1 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_1 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     */
    @Query(value = "select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order s ) d ", nativeQuery = true)
    List<TbOrder> caseWhenValid01();

    /**
     * 不支持的 SQL：
     * CASE WHEN 中包含子查询 验证
     * Actual SQL: ds_0 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_0 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     * Actual SQL: ds_1 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_0 s ) d
     * Actual SQL: ds_1 ::: select case when (select id from address) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order_1 s ) d
     */
    @Query(value = "select case when (select oo.id from address oo) = 1 then 1 else d.order_id end as order_id , d.user_id from ( select s.order_id, s.user_id from tb_order s ) d ", nativeQuery = true)
    List<TbOrder> caseWhenValid02();
}
